const db = require('../model/database')
const query = require('../model/query')

// 获取学院以及专业列表
exports.getMajorList = async (req, res, next) => {
  // 获取学院列表
  const sql = 'SELECT * FROM college'
  const collegeList = await query(sql, null, db)
  // 获取专业列表
  const sql2 = 'SELECT * FROM major'
  const majorList = await query(sql2, null, db)
  // 查询专业人数
  const sql3 = 'SELECT COUNT(*) AS total, major_id FROM student GROUP BY major_id'
  const majorTotalList = await query(sql3, null, db)
  // 将专业人数添加到专业中
  majorList.forEach(major => {
    majorTotalList.forEach(majorTotal => {
      if (major.id === majorTotal.major_id) {
        major.total = majorTotal.total
      }
    })
  })
  // 修改专业的name字段为major
  majorList.forEach(major => {
    major.major = major.name
    delete major.name
  })
  // 将专业添加到学院中
  collegeList.forEach(college => {
    college.children = []
    majorList.forEach(major => {
      if (college.id === major.college_id) {
        major.id = college.id + '-' + major.id
        college.children.push(major)
      }
    })
  })
  // 整理数据
  collegeList.forEach(college => {
    delete college.phone
    delete college.email
    delete college.total
  })
  res.send({
    code: 20000,
    data: collegeList,
  })
}

// 查询所有专业
exports.getMajorListAll = async (req, res, next) => {
  const sql = 'SELECT * FROM major'
  const result = await query(sql, null, db)
  res.send({
    code: 20000,
    data: result
  })
}

// 修改专业信息
exports.updateMajor = async (req, res, next) => {
  const { id, major } = req.body
  // 验证数据
  if (!id || !major) {
    res.send({
      code: 40000,
      message: '参数错误'
    })
  }
  // 修改专业信息
  const sql = 'UPDATE major SET name = ? WHERE id = ?'
  const params = [major, id]
  const result = await query(sql, params, db)
  if (result.affectedRows === 1) {
    res.send({
      code: 20000,
      message: '修改成功'
    })
  }
}

// 根据学院id查询专业
exports.getMajorListByCollegeId = async (req, res, next) => {
  const { collegeId } = req.query
  // 验证数据
  if (!collegeId) {
    res.send({
      code: 40000,
      message: '参数错误'
    })
  }
  // 查询专业
  const sql = 'SELECT * FROM major WHERE college_id = ?'
  const params = [collegeId]
  const result = await query(sql, params, db)
  res.send({
    code: 20000,
    data: result
  })
}

// 添加专业
exports.addMajor = async (req, res, next) => {
  const { collegeId, name } = req.body
  // 验证数据
  if (!collegeId || !name) {
    res.send({
      code: 40000,
      message: '参数错误'
    })
  }
  // 添加专业
  const sql = 'INSERT INTO major (college_id, name) VALUES (?, ?)'
  const params = [collegeId, name]
  const result = await query(sql, params, db)
  if (result.affectedRows === 1) {
    res.send({
      code: 20000,
      message: '添加成功'
    })
  }
}

// 删除专业
exports.deleteMajor = async (req, res, next) => {
  const { majorId } = req.query
  // 验证数据
  if (!majorId) {
    res.send({
      code: 40000,
      message: '参数错误'
    })
  }

  // 删除专业
  const sql = 'DELETE FROM major WHERE id = ?'
  const params = [majorId]
  const result = await query(sql, params, db)
  if (result.affectedRows === 1) {
    res.send({
      code: 20000,
      message: '删除成功'
    })
  }
}
